<?php

class CustReportsController extends AppController {

    var $name = 'CustReports';
    var $pageTitle = 'Customer Report';
    var $viewPath = 'customers/reports';
    var $uses = array('CustStockOutHead', 'CustProductSite', 'Customer', 'Site',  'CustomerSupplier', 'Supplier', 'Category', 'SiteCostCenter');

    function beforeFilter() {
        parent::beforeFilter();
        $this->set('parentNav', 'cust_reports');
        $this->set('tabMenu', 'tabmenus/customer/reports');
    }

    function display() {

    }

    function setStockOutOrderOptions() {
        // เงื่อนไขใน Order by
        if ($this->Auth->user('hide_price') == false) {
            $this->set('orderOptions', array(
                'CustProductSite.stock_code' => 'Stock Code',
                'CustStockOut.stock_out_avg_price' => 'Price',
                'frequency' => 'Frequency',
                'stock_out_qty' => 'Quantity',
                '(CustStockOut.stock_out_avg_price * SUM(stock_out_qty))' => 'Total Price',
                '(CustProductSite.last_stock_out_date)' => 'Stock out Date',
            ));
        } else {
            $this->set('orderOptions', array(
                'CustProductSite.stock_code' => 'Stock Code',
                'frequency' => 'Frequency',
                'stock_out_qty' => 'Quantity',
                '(CustProductSite.last_stock_out_date)' => 'Stock out Date',
            ));
        }

        // เรียงจากน้อยไปมาก หรือ มากไปน้อย
        $this->set('seqOptions', array(
            'asc' => 'Ascendent',
            'desc' => 'Descendent'
        ));
    }

    /**
     * หน้าหลัก report Customer Stock out
     */
    function stock_out_search() {
        $this->viewPath = 'customers/reports/stock_out';
        $this->pageTitle = 'Customer Stock out Report<br />Group by Part';
        $this->set('categories', $this->Category->listCategoryBySiteOrSupplier($this->Auth->user('site_id'), $this->Auth->user('supplier_id')));

        // ถ้าเป็น Vendor เข้ามาดุเห็นเฉพาะ brand ของตัวเอง
        if ($this->Auth->user('prefix') != SUPPLIER_PREFIX) {
            $suppliers = $this->CustProductSite->listSupplierByCustomerId($this->Auth->user('customer_id'));
        } else {
            $suppliers = $this->Auth->user('supplier_id');
        }
        $this->set('brands', $this->CustProductSite->Product->Brand->find('list', array('conditions' => array('supplier_id' => $suppliers), 'order' => 'name')));
        $this->set('suppliers', $this->CustomerSupplier->listSupplierByCustomerId($this->Auth->user('customer_id'), array('has_cust_inventory' => true)));
        $this->setStockOutOrderOptions();
    }

    /**
     * function ประมวลผลเงื่อนไขการค้นหา Customer stock out
     */
    function stock_out_query() {
        $this->viewPath = 'customers/reports/stock_out';

        $site_id = $this->Auth->user('site_id');

        // set ตัวแปรให้หน้า view ว่าต้องโชว์ stock out no กับ remark หรือไม่
        $this->set('display_stock_out_no', $this->data['Option']['display_stock_out_no']);
        $this->data = $this->CustProductSite->reportCustStocksOut($site_id, $this->data);
    }

    function stock_out_print() {
        $this->viewPath = 'customers/reports/stock_out';

        // ถ้ามีการค้นหาตาม Category ดึงชื่อ Category มาใส่ตัวแปรให้หน้า view
        if (!empty($this->data['Product']['category_id'])) {
            $this->CustProductSite->Product->Category->id = $this->data['Product']['category_id'];
            $this->set('category_name', $this->CustProductSite->Product->Category->field('name'));
        }

        if (!empty($this->data['Product']['brand_id'])) {
            $this->CustProductSite->Product->Brand->id = $this->data['Product']['brand_id'];
            $this->set('brand_name', $this->CustProductSite->Product->Brand->field('name'));
        }

        if (!empty($this->data['CustStockOutHead']['dispatch_from'])) {
            if ($this->data['CustStockOutHead']['dispatch_from'] == ITS_PREFIX) {
                $this->set('vendor', $this->requestAction('system_preferences/get/company_name'));
            } else {
                if ($this->data['CustStockOutHead']['supplier_id'] != null) {
                    $this->CustStockOutHead->Supplier->id = $this->data['CustStockOutHead']['supplier_id'];
                    $this->set('vendor', $this->CustStockOutHead->Supplier->field('name'));
                } else {
                    $this->set('vendor', 'Other Vendor');
                }
            }
        }

        $this->_setReportPrintNote();
        $this->setStockOutOrderOptions();

        $this->stock_out_query();
    }

    function stock_out_excel() {
        $this->stock_out_print();
        parent::excel('VMI Customer Stock Out Report Group by Part');
    }

//    /**
//     * สำหรับ Stock out report กรณีมีการแสดง stock out no และ remark
//     */
//    function get_stock_out_no_remark($cust_product_site_id, $cust_stock_out_price) {
//        $site_cost_center_id = isset($this->passedArgs['site_cost_center_id']) ? $this->passedArgs['site_cost_center_id'] : null;
//        $site_id = $this->Auth->user('site_id');
//        $sql = "SELECT CustStockOutHead.id, stock_out_no, remark, stock_out_qty, return_qty, net_stock_out_qty, DATE_FORMAT(stock_out_date, '%d %M %Y') stock_out_date,
//                       SiteCostCenter.name
//                FROM pre_cust_products_sites CustProductSite JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
//                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
//                JOIN pre_categories Category ON (Category.id = Product.category_id)
//                JOIN pre_cust_stocks_out CustStockOut ON (CustStockOut.cust_product_site_id = CustProductSite.id)
//                JOIN pre_cust_stock_out_heads CustStockOutHead ON (CustStockOut.cust_stock_out_head_id = CustStockOutHead.id)
//                LEFT JOIN pre_site_cost_centers SiteCostCenter ON (CustStockOutHead.site_cost_center_id = SiteCostCenter.id)";
//        $sql .= " WHERE CustProductSite.site_id = {$site_id}
//                AND CustStockOut.stock_out_avg_price = {$cust_stock_out_price}
//                AND CustProductSite.id = {$cust_product_site_id} ";
//
//        if ($site_cost_center_id) {
//            $sql .= " AND SiteCostCenter.id = " . $site_cost_center_id;
//        }
//
//        // เอาเงื่อนไขจากการ search มาใส่เพื่ม
//        $sql_condition = $this->Session->read($this->name . 'sql');
//        $sql .= $sql_condition;
////        debug($sql);
//        $data = $this->CustProductSite->query($sql);
////        debug($data);
//        return $data;
//    }

    /**
     * กำหนดค่าตัวแปรให้แก่หน้า view
     */
    function _setReportPrintNote() {
        $this->layout = 'print';
        $customer_id = $this->Auth->user('customer_id');
        $site_id = $this->Auth->user('site_id');
        $this->Customer->id = $customer_id;
        $this->Site->id = $this->Auth->user('site_id');
        $this->set('customer_no', $this->Customer->field('cust_no'));
        $this->set('customer_name', $this->Customer->field('name'));
        $this->set('site_no', $this->Site->field('site_no'));
        $this->set('site_name', $this->Site->field('name'));
        $this->set('report_date', date('d/m/Y'));
        $this->set('user', $this->Auth->user('name'));

        // เก็บเงื่อนไขที่ส่งมาลงตัวแปรให้หน้า view ตรวจสอบ
        $this->set('criteria', $this->data);
        $this->set('print_orientation', PRINT_LANDSCAPE);
    }

    function setStockInOrderOptions() {
        // เงื่อนไขใน Order by
        if ($this->Auth->user('hide_price') == false) {
            $this->set('orderOptions', array(
                'CustProductSite.stock_code' => 'Stock Code',
                'CustStockIn.quotation_price' => 'Price',
                'frequency' => 'Frequency',
                'stock_in_qty' => 'Quantity',
                '(CustStockIn.quotation_price * SUM(stock_in_qty))' => 'Total Price',
                '(CustProductSite.last_stock_in_date)' => 'Stock in Date',
            ));
        } else {
            $this->set('orderOptions', array(
                'CustProductSite.stock_code' => 'Stock Code',
                'frequency' => 'Frequency',
                'stock_in_qty' => 'Quantity',
                '(CustProductSite.last_stock_in_date)' => 'Stock in Date',
            ));
        }

        // เรียงจากน้อยไปมาก หรือ มากไปน้อย
        $this->set('seqOptions', array(
            'asc' => 'Ascendent',
            'desc' => 'Descendent'
        ));
    }

    /**
     * หน้าหลัก report Customer Stock out
     */
    function stock_in_search() {
        $this->viewPath = 'customers/reports/stock_in';
        $this->pageTitle = 'Customer Stock in Report';
        $this->set('categories', $this->Category->listCategoryBySiteOrSupplier($this->Auth->user('site_id'), $this->Auth->user('supplier_id')));

        // ถ้าเป็น Vendor เข้ามาดุเห็นเฉพาะ brand ของตัวเอง
        if ($this->Auth->user('prefix') != SUPPLIER_PREFIX) {
            $suppliers = $this->CustProductSite->listSupplierByCustomerId($this->Auth->user('customer_id'));
        } else {
            $suppliers = $this->Auth->user('supplier_id');
        }

        $this->set('brands', $this->CustProductSite->Product->Brand->find('list', array('conditions' => array('supplier_id' => $suppliers), 'order' => 'name')));
        $this->set('suppliers', $this->CustomerSupplier->listSupplierByCustomerId($this->Auth->user('customer_id'), array('has_cust_inventory' => true)));

//        $this->setStockInOrderOptions();
    }

    /**
     * function ประมวลผลเงื่อนไขการค้นหา Customer stock out
     */
    function stock_in_query() {
        $this->viewPath = 'customers/reports/stock_in';

        $site_id = $this->Auth->user('site_id');

        // set ตัวแปรให้หน้า view ว่าต้องโชว์ stock out no หรือไม่
        $this->set('display_stock_in_no', $this->data['Option']['display_stock_in_no']);
        $this->data = $this->CustProductSite->reportCustStocksIn($site_id, $this->data);
    }

    function stock_in_print() {
        $this->viewPath = 'customers/reports/stock_in';
        // ถ้ามีการค้นหาตาม Category ดึงชื่อ Category มาใส่ตัวแปรให้หน้า view
        if (!empty($this->data['Product']['category_id'])) {
            $this->CustProductSite->Product->Category->id = $this->data['Product']['category_id'];
            $this->set('category_name', $this->CustProductSite->Product->Category->field('name'));
        }

        if (!empty($this->data['Product']['brand_id'])) {
            $this->CustProductSite->Product->Brand->id = $this->data['Product']['brand_id'];
            $this->set('brand_name', $this->CustProductSite->Product->Brand->field('name'));
        }

        if (!empty($this->data['CustStockInHead']['dispatch_from'])) {
            if ($this->data['CustStockInHead']['dispatch_from'] == ITS_PREFIX) {
                $this->set('vendor', $this->requestAction('system_preferences/get/company_name'));
            } else {
                if ($this->data['CustStockInHead']['supplier_id'] != null) {
                    $this->Supplier->id = $this->data['CustStockInHead']['supplier_id'];
                    $this->set('vendor', $this->Supplier->field('name'));
                } else {
                    $this->set('vendor', 'Other Vendor');
                }
            }
        }

//        $this->setStockInOrderOptions();
        $this->_setReportPrintNote();
        $this->stock_in_query();
    }

    function stock_in_excel() {
        $this->stock_in_print();
        parent::excel('VMI Customer Stock In Report');
    }

    /**
     * หน้าหลัก report Customer Movement Inventory
     * จำนวน Current stock ปัจจุบัน
     */
    function movement_inventory_search() {
        $this->viewPath = 'customers/reports/movement_inventory';
        $this->pageTitle = 'Movement Inventory Report';

        $this->set('categories', $this->CustProductSite->Product->Category->find('list', array('order' => 'name')));
        $this->set('suppliers', $this->Product->Supplier->find('list', array('order' => 'name')));
        $this->set('operands', array('<' => '<', '<=' => '<=', '=' => '=', '>' => '>', '>=' => '>='));

        $this->_setDispatchOrderOptions();
    }

    function stock_out_cost_center_search() {
        $this->viewPath = 'customers/reports/stock_out_cost_center';
        $this->pageTitle = 'Customer Stock out Report<br />Group by Cost Center';

        $this->setStockOutCostCenterOrderOptions();
        $this->set('costCenters', $this->SiteCostCenter->find('list', array('conditions' => array('site_id' => $this->Auth->user('site_id')))));
    }

    function setStockOutCostCenterOrderOptions() {
        // เงื่อนไขใน Order by
        $this->set('orderOptions', array(
            'SiteCostCenter.name' => 'Cost Center',
            'frequency' => 'Frequency',
            'stock_out_qty' => 'Quantity',
            '(CustStockOut.stock_out_avg_price * SUM(stock_out_qty))' => 'Total Price',
        ));

        // เรียงจากน้อยไปมาก หรือ มากไปน้อย
        $this->set('seqOptions', array(
            'asc' => 'Ascendent',
            'desc' => 'Descendent'
        ));
    }

    function stock_out_cost_center_query() {
        $this->viewPath = 'customers/reports/stock_out_cost_center';

        // set ตัวแปรให้หน้า view ว่าต้องโชว์ stock out no กับ remark หรือไม่
        $this->set('display_stock_out_no', $this->data['Option']['display_stock_out_no']);

        $site_id = $this->Auth->user('site_id');
        $this->data = $this->SiteCostCenter->reportStockOutByCostCenter($site_id, $this->data, $this->data['Option']['display_stock_out_no']);
    }

    function stock_out_cost_center_print() {
        $this->viewPath = 'customers/reports/stock_out_cost_center';

        // ถ้ามีการค้นหาตาม CostCenter ดึงชื่อ CostCenter มาใส่ตัวแปรให้หน้า view
        if (!empty($this->data['SiteCostCenter']['id'])) {
            $this->SiteCostCenter->id = $this->data['SiteCostCenter']['id'];
            $this->set('cost_center_name', $this->SiteCostCenter->field('name'));
        }

        $this->_setReportPrintNote();
        $this->setStockOutCostCenterOrderOptions();

        $this->stock_out_cost_center_query();
    }

    function stock_out_cost_center_excel() {
        $this->stock_out_cost_center_print();
        parent::excel('VMI Customer Stock Out Report Group by Cost Center');
    }

}

?>
